* Technical validation - comparing turnover (TurnoverPct) with external data (Turnover)

* Import "LALD" file in .xlsx or .dta

import excel "/Users/PathFile/LALD.xlsx", sheet("Sheet1") firstrow
use "/Users/PathFile/LALD.dta"

* Create new variable to count the number of incumbents
gen Incumbents = ImmediateReelection == 1
collapse (sum) Incumbents (count) TotalLegislators = ImmediateReelection, by(Country CountryID ElectionYear ChamberType)

* Calculate the turnover rate
gen TurnoverPct = (100) - (Incumbents / TotalLegislators) * 100
gen Turnover = .

* Legislatina USAL Argentina
replace Turnover = 82.9 if CountryID == 11 & ElectionYear == 2003 & ChamberType == 0
replace Turnover = 86.9 if CountryID == 11 & ElectionYear == 2005 & ChamberType == 0
replace Turnover = 80.4 if CountryID == 11 & ElectionYear == 2007 & ChamberType == 0
replace Turnover = 81.1 if CountryID == 11 & ElectionYear == 2009 & ChamberType == 0

* Legislatina USAL Bolivia
replace Turnover = 88.9 if CountryID == 12 & ElectionYear == 2002 & ChamberType == 1
replace Turnover = 96.3 if CountryID == 12 & ElectionYear == 2005 & ChamberType == 1

* Legislatina USAL Brazil
replace Turnover = 45.8 if CountryID == 13 & ElectionYear == 2002 & ChamberType == 0
replace Turnover = 52.2 if CountryID == 13 & ElectionYear == 2006 & ChamberType == 0
replace Turnover = 50.6 if CountryID == 13 & ElectionYear == 2002 & ChamberType == 1
replace Turnover = 39.5 if CountryID == 13 & ElectionYear == 2006 & ChamberType == 1

* Legislatina USAL Colombia
replace Turnover = 67.9 if CountryID == 14 & ElectionYear == 2006 & ChamberType == 0
replace Turnover = 63.7 if CountryID == 14 & ElectionYear == 2006 & ChamberType == 1

* Legislatine USAL Dominican Republic
replace Turnover = 86.6 if CountryID == 16 & ElectionYear == 2002 & ChamberType == 0
replace Turnover = 53.3 if CountryID == 16 & ElectionYear == 2006 & ChamberType == 0
replace Turnover = 75.9 if CountryID == 16 & ElectionYear == 2002 & ChamberType == 1
replace Turnover = 84.4 if CountryID == 16 & ElectionYear == 2006 & ChamberType == 1

* Legislatina USAL Ecuador
replace Turnover = 82.6 if CountryID == 17 & ElectionYear == 2002 & ChamberType == 0
replace Turnover = 93.9 if CountryID == 17 & ElectionYear == 2006 & ChamberType == 0

* Legislatina USAL El Salvador
replace Turnover = 58.4 if CountryID == 18 & ElectionYear == 2003 & ChamberType == 0
replace Turnover = 54.8 if CountryID == 18 & ElectionYear == 2006 & ChamberType == 0

* Legislatina USAL Guatemala
replace Turnover = 72.3 if CountryID == 19 & ElectionYear == 1999 & ChamberType == 0
replace Turnover = 92.9 if CountryID == 19 & ElectionYear == 2003 & ChamberType == 0
replace Turnover = 94.4 if CountryID == 19 & ElectionYear == 2007 & ChamberType == 0

* Legislatina USAL Honduras
replace Turnover = 71.9 if CountryID == 20 & ElectionYear == 2001 & ChamberType == 0
replace Turnover = 79.5 if CountryID == 20 & ElectionYear == 2005 & ChamberType == 0

* Legislatina USAL Chile
replace Turnover = 49 if CountryID == 21 & ElectionYear == 2001 & ChamberType == 1
replace Turnover = 42.9 if CountryID == 21 & ElectionYear == 2005 & ChamberType == 1

* Legislatina USAL Nicaragua
replace Turnover = 78.3 if CountryID == 23 & ElectionYear == 2001 & ChamberType == 0
replace Turnover = 68.4 if CountryID == 23 & ElectionYear == 2006 & ChamberType == 0

* Legislatina USAL Paraguay
replace Turnover = 81.2 if CountryID == 25 & ElectionYear == 2003 & ChamberType == 0
replace Turnover = 68.7 if CountryID == 25 & ElectionYear == 2008 & ChamberType == 0
replace Turnover = 82.2 if CountryID == 25 & ElectionYear == 2003 & ChamberType == 1
replace Turnover = 75.6 if CountryID == 25 & ElectionYear == 2008 & ChamberType == 1

* Legislatina USAL Peru
replace Turnover = 75 if CountryID == 26 & ElectionYear == 2000 & ChamberType == 0

* Dos Santos 2006
replace Turnover = 71.8 if CountryID == 11 & ElectionYear == 1985 & ChamberType == 0
replace Turnover = 80.3 if CountryID == 11 & ElectionYear == 1987 & ChamberType == 0
replace Turnover = 81.6 if CountryID == 11 & ElectionYear == 1989 & ChamberType == 0
replace Turnover = 76.8 if CountryID == 11 & ElectionYear == 1991 & ChamberType == 0
replace Turnover = 77.1 if CountryID == 11 & ElectionYear == 1993 & ChamberType == 0
replace Turnover = 73.4 if CountryID == 11 & ElectionYear == 1995 & ChamberType == 0
replace Turnover = 66.9 if CountryID == 11 & ElectionYear == 1997 & ChamberType == 0
replace Turnover = 76.4 if CountryID == 11 & ElectionYear == 1999 & ChamberType == 0
replace Turnover = 69.3 if CountryID == 11 & ElectionYear == 2001 & ChamberType == 0

* Centellas 2013
replace Turnover = 72.3 if CountryID == 12 & ElectionYear == 1985 & ChamberType == 0
replace Turnover = 68.5 if CountryID == 12 & ElectionYear == 1989 & ChamberType == 0
replace Turnover = 61.8 if CountryID == 12 & ElectionYear == 1993 & ChamberType == 0
replace Turnover = 78.5 if CountryID == 12 & ElectionYear == 1997 & ChamberType == 0
replace Turnover = 86.9 if CountryID == 12 & ElectionYear == 2002 & ChamberType == 0
replace Turnover = 90.8 if CountryID == 12 & ElectionYear == 2005 & ChamberType == 0
replace Turnover = 97.7 if CountryID == 12 & ElectionYear == 2009 & ChamberType == 0

* Duque 2011
replace Turnover = 72.1 if CountryID == 14 & ElectionYear == 2010 & ChamberType == 0

* Mejía Acosta 2004
replace Turnover = 72 if CountryID == 17 & ElectionYear == 1996 & ChamberType == 0
replace Turnover = 81.8 if CountryID == 17 & ElectionYear == 1998 & ChamberType == 0

* Gonzalez-Bustamante & Cisternas 2016
replace Turnover = 41.7 if CountryID == 21 & ElectionYear == 1993 & ChamberType == 0
replace Turnover = 39.2 if CountryID == 21 & ElectionYear == 1997 & ChamberType == 0
replace Turnover = 38.3 if CountryID == 21 & ElectionYear == 2001 & ChamberType == 0
replace Turnover = 35.8 if CountryID == 21 & ElectionYear == 2005 & ChamberType == 0
replace Turnover = 38.3 if CountryID == 21 & ElectionYear == 2009 & ChamberType == 0

* Campos & Diaz 2021
replace Turnover = 70 if CountryID == 26 & ElectionYear == 2001 & ChamberType == 0
replace Turnover = 84 if CountryID == 26 & ElectionYear == 2006 & ChamberType == 0
replace Turnover = 81 if CountryID == 26 & ElectionYear == 2011 & ChamberType == 0
replace Turnover = 78 if CountryID == 26 & ElectionYear == 2016 & ChamberType == 0
replace Turnover = 97 if CountryID == 26 & ElectionYear == 2020 & ChamberType == 0

* Parlamentodata.com
replace Turnover = 68 if CountryID == 27 & ElectionYear == 1989 & ChamberType == 0
replace Turnover = 70 if CountryID == 27 & ElectionYear == 1994 & ChamberType == 0
replace Turnover = 57 if CountryID == 27 & ElectionYear == 1999 & ChamberType == 0
replace Turnover = 56 if CountryID == 27 & ElectionYear == 2004 & ChamberType == 0
replace Turnover = 51 if CountryID == 27 & ElectionYear == 2009 & ChamberType == 0
replace Turnover = 51 if CountryID == 27 & ElectionYear == 2014 & ChamberType == 0
replace Turnover = 51 if CountryID == 27 & ElectionYear == 2019 & ChamberType == 0
replace Turnover = 58 if CountryID == 27 & ElectionYear == 1989 & ChamberType == 1
replace Turnover = 65 if CountryID == 27 & ElectionYear == 1994 & ChamberType == 1
replace Turnover = 45 if CountryID == 27 & ElectionYear == 1999 & ChamberType == 1
replace Turnover = 55 if CountryID == 27 & ElectionYear == 2004 & ChamberType == 1
replace Turnover = 39 if CountryID == 27 & ElectionYear == 2009 & ChamberType == 1
replace Turnover = 52 if CountryID == 27 & ElectionYear == 2014 & ChamberType == 1
replace Turnover = 61 if CountryID == 27 & ElectionYear == 2019 & ChamberType == 1

* Variable to measure difference between both Turnover measures
gen Difference = Turnover - TurnoverPct

pwcorr Turnover TurnoverPct
scatter Turnover TurnoverPct
